/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utility;

import DTO.ListPlaces;
import DTO.Place;
import DTO.PlaceInList;
import DTO.Places;
import DTO.Province;
import DTO.Provinces;
import DTO.Tag;
import DTO.Tags;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author SQL
 */
public class DBUtils {

    public static void editPlacePicturePath(int id, String newPicturePath) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = makeConnection();
            String sql = "UPDATE Place set Place_PicturePath=? Where Place_Id=?";
            stm = con.prepareStatement(sql);            
            stm.setString(1, newPicturePath);
            stm.setInt(2, id);
            stm.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void editPlaceByID(int id, String newDescription, String newPhone, String newAddress) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = makeConnection();
            String sql = "UPDATE Place set Place_Description=?, Place_Phone=?, Place_Address=? Where Place_Id=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, newDescription);
            stm.setString(2, newPhone);
            stm.setString(3, newAddress);
            stm.setInt(4, id);
            stm.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void addPlace(Place place) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = makeConnection();
            String sql = "Insert Into Place (Place_Name,Province_ID,Place_PicturePath,Place_Description,Place_Address,Place_Phone) VALUES(?,?,?,?,?,?)";
            stm = con.prepareStatement(sql);
            stm.setString(1, place.getName());
            stm.setInt(2, place.getProvinceId());
            stm.setString(3, place.getPicturePath());
            stm.setString(4, place.getPlaceDescription());
            stm.setString(5, place.getPlaceAddress());
            stm.setString(6, place.getPlacePhone());
            stm.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static boolean updatePoint(int placeTagID, int newPoint) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = makeConnection();
            String sql = "Update Place_Tag set Point = ? Where Place_Tag_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, newPoint);
            stm.setInt(2, placeTagID);
            int row = stm.executeUpdate();

            if (row > 0) {
                return true;
            } else {
                //bung loi
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public static Connection makeConnection() {
        Connection con = null;
        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;databaseName=TOURDB;instanceName=CHAULVN60511/MSSQLSERVER2008";
            con = DriverManager.getConnection(url, "sa", "123456");
            return con;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
//        } finally {
//            try {
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
        }
        return null;
    }

    public static String getProvinceNameByID(int id) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String result = "";
        try {
            con = makeConnection();
            String sql = "select Province_Name from Province where Province_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, id);
            rs = stm.executeQuery();
            if (rs.next()) {
                result = rs.getString(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return result;
        }
    }

    public static Provinces loadProvinced() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String sql = "Select * From Province";
            stm = con.prepareStatement(sql);

            rs = stm.executeQuery();
            Provinces list = new Provinces();

            while (rs.next()) {
                int id = rs.getInt("Province_ID");
                String name = rs.getString("Province_Name");
                String picturePath = rs.getString("Province_PicturePath");
                list.getProvince().add(new Province(id, name, picturePath));
            }

            return list;

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static ListPlaces loadListPlacesbyProvinceID(int provinceID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String sql = "Select Place_ID,Place_Name,Place_PicturePath From Place Where Province_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, provinceID);
            rs = stm.executeQuery();
            ListPlaces list = new ListPlaces();

            while (rs.next()) {
                int place_ID = rs.getInt("Place_ID");
                String place_Name = rs.getString("Place_Name");
                String place_PicturePath = rs.getString("Place_PicturePath");
                PlaceInList place = new PlaceInList(place_ID, place_Name, place_PicturePath);
                list.getPlaceInList().add(place);
            }
            return list;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static Place loadPlacebyPlaceID(int placeID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String sql = "Select * From Place Where place_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, placeID);
            rs = stm.executeQuery();
            Place place;
            while (rs.next()) {

                int place_ID = rs.getInt("Place_ID");
                String place_Name = rs.getString("Place_Name");
                int province_ID = rs.getInt("Province_ID");
                String place_PicturePath = rs.getString("Place_PicturePath");
                String Place_Description = rs.getString("Place_Description");
                String Place_Address = rs.getString("Place_Address");
                String Place_Phone = rs.getString("Place_Phone");
                Tags tags = getTagsByPlaceID(place_ID);
                place = new Place(placeID, place_Name, province_ID,
                        place_PicturePath, Place_Description,
                        Place_Address, Place_Phone, tags);
                return place;

            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static Tags getTagsByPlaceID(int place_ID) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = makeConnection();
            String sql = "SELECT t.Tag_ID, t.Tag_Name, t.Tag_Type, pt.Point, pt.Place_Tag_ID FROM "
                    + "Tag t, Place_Tag pt WHERE pt.Place_ID=? AND pt.Tag_ID = t.Tag_ID";
            stm = con.prepareStatement(sql);
            stm.setInt(1, place_ID);
            rs = stm.executeQuery();
            Tags list = new Tags();

            while (rs.next()) {
                int id = rs.getInt("Tag_ID");

                String tag_Name = rs.getString("Tag_Name");
                boolean tag_Type = rs.getBoolean("Tag_Type");
                int point = rs.getInt("Point");
                int place_tag_id = rs.getInt("Place_Tag_ID");
                Tag tag = new Tag(id, tag_Name, tag_Type, point, place_tag_id);
                list.getTag().add(tag);
            }

            return list;

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

     public static void deletePlace(int Place_ID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = makeConnection();
            String sql = "Delete From Place Where Place_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Place_ID);
            int row = stm.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void deletePlaceByProvinceID(int Province_ID) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = makeConnection();
            String sql = "Delete From Place Where Province_ID = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, Province_ID);
            int row = stm.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
//    public static boolean checkLogin(String username, String password) {
//        Connection con = null;
//        PreparedStatement stm = null;
//        ResultSet rs = null;
//        try {
//            con = makeConnection();
//            String sql = "Select * From tb_Info Where username = ? and password = ?";
//            stm = con.prepareStatement(sql);
//            stm.setString(1, username);
//            stm.setString(2, password);
//            rs = stm.executeQuery();
//            if (rs.next()) {
//                return true;
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return false;
//    }
//
//    public static AccountDTO[] searchLikeLastname(String name) {
//        Connection con = null;
//        PreparedStatement stm = null;
//        ResultSet rs = null;
//        try {
//            con = makeConnection();
//            String sql = "Select * From tb_Info Where lastname Like ?";
//            stm = con.prepareStatement(sql);
//            stm.setString(1, "%" + name + "%");
//            rs = stm.executeQuery();
//            List list = new ArrayList();
//
//            while (rs.next()) {
//                String username = rs.getString("username");
//                String password = rs.getString("password");
//                String lastname = rs.getString("lastname");
//                boolean roles = rs.getBoolean("isAdmin");
//                AccountDTO acc = new AccountDTO(username, password, lastname, roles);
//                list.add(acc);
//            }
//            AccountDTO[] result = new AccountDTO[list.size()];
//            list.toArray(result);
//            return result;
//
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return null;
//    }
//
//    public static boolean deleteAccount(String username) {
//        Connection con = null;
//        PreparedStatement stm = null;
//
//        try {
//            con = makeConnection();
//            String sql = "Delete From tb_Info Where username = ?";
//            stm = con.prepareStatement(sql);
//            stm.setString(1, username);
//
//            int row = stm.executeUpdate();
//
//            if (row > 0) {
//                return true;
//            }
//
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return false;
//    }
//
//    public static boolean updateAccount(String username, String password, boolean roles) {
//        Connection con = null;
//        PreparedStatement stm = null;
//
//        try {
//            con = makeConnection();
//            String sql = "Update tb_Info Set password = ?, isAdmin = ? Where username = ?";
//            stm = con.prepareStatement(sql);
//            stm.setString(1, password);
//            stm.setBoolean(2, roles);
//            stm.setString(3, username);
//
//            int row = stm.executeUpdate();
//
//            if (row > 0) {
//                return true;
//            }
//
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return false;
//    }
//
//    public static boolean insertAccount(String username, String password, String lastname, boolean roles) {
//        Connection con = null;
//        PreparedStatement stm = null;
//        //Statement state = null;
//
//
//        try {
//            con = makeConnection();
//            String sql = "INSERT INTO tb_Info (username,password,lastname,isAdmin) "
//                    + "VALUES (?,?,?,?)";
//            stm = con.prepareStatement(sql);
//            stm.setString(1, username);
//            stm.setString(2, password);
//            stm.setString(3, lastname);
//            stm.setBoolean(4, roles);
//            int row = stm.executeUpdate();
//            if (row > 0) {
//                return true;
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (stm != null) {
//                    stm.close();
//                }
//                if (con != null) {
//                    con.close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return false;
//    }
}
